// Photo Database routines
// Copyright 2006 (C) Ralph Thomas

#include <apps/photo/photodb.h>
#include <util/db.h>
#include <boost/filesystem/operations.hpp>
#include <boost/filesystem/convenience.hpp>
#include <stdlib.h> // for getenv
#include <stdexcept>
#include <sstream>

namespace bfs = boost::filesystem;

namespace apps {
	namespace photo {
		std::string escape_db( const std::string& in ) {
			return util::db::escape( in );
		}
		bfs::path getPhotoLibraryPath() {
			bfs::path p( getenv( "HOME" ), bfs::native );
			return p / bfs::path( "Photo Library", bfs::native );
		}
		bfs::path getPhotoDatabasePath() {
			return getPhotoLibraryPath() / "photo.db";
		}
		bool init_database( const bfs::path& path ) {
			bfs::path db_path( path / "photo.db" );
			//
			// Try to create the path if it doesn't exist.
			// 
			if( !bfs::exists( path ) && !bfs::create_directories( path ) ) return false;
			//
			// If the database exists then assume that it's
			// okay.
			//
			if( bfs::exists( db_path ) ) return true;
			//
			// Create the database.
			// 
			util::db_ptr_t db( util::db::fromPath( db_path ) );
			//
			// If we don't have a database around then we can't
			// go any further.
			//
			if( !db ) return false;
			//
			// Create all of our tables.
			//
			try {
				db->execute( "create table photos (id INTEGER PRIMARY KEY, filename TEXT, rating INTEGER, exif_date DATE, import_date DATE, sum TEXT);" );
				db->execute( "create table photo_tag_map (photo_id INTEGER, tag_id INTEGER);" );
				db->execute( "create table tags (id INTEGER PRIMARY KEY, name TEXT);" );
				db->execute( "create trigger insert_photo after insert on photos begin update photos set import_date = datetime('now') where rowid = new.rowid; end;" );
			} catch( ... ) { return false; }
			return true;
		}
		struct tag_entry_t {
			bool exists;
			std::string id;
			std::string name;

			tag_entry_t() : exists( false ) {}
			static void from_dictionary( tag_entry_t& te, const adobe::dictionary_t& dict ) {
				te.exists = true;
				te.id = dict.get<std::string>( adobe::static_name_t( "id" ) );
				te.name = dict.get<std::string>( adobe::static_name_t( "name" ) );
			}
		};
		std::string add_tag( const std::string& tag ) {
			util::db_ptr_t db( util::db::fromPath( getPhotoDatabasePath() ) );
			if( !db ) throw std::runtime_error( "unable to open photo database" );
			//
			// Query to see if this tag already exists in the database.
			//
			tag_entry_t te;
			std::string escape_tag( escape_db( tag ) );
			db->query( "select * from tags where name = '" + escape_tag + "';", boost::bind( &tag_entry_t::from_dictionary, boost::ref( te ), _1 ) );
			if( te.exists ) return te.id;
			//
			// This tag doesn't already exist, create it.
			//
			std::stringstream ss;
			ss << db->execute( "insert into tags (name) values ('" + escape_tag + "');" );
			return ss.str();
		}
	}
}

